{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "字段 | 说明 \n",
    "------|------\n",
    "competition_process|世界杯赛程\n",
    "id|赛程id\n",
    "home_id|主队id，对应team_info中的id\n",
    "visiting_id|客队id，对应team中的id\n",
    "home_team|主场队伍\n",
    "visiting_team|客场队伍\n",
    "competitiong_time|比赛时间\n",
    "result|比赛结果\n",
    "type|比赛类型（小组赛，淘汰赛等）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>home_id</th>\n",
       "      <th>visiting_id</th>\n",
       "      <th>home_team</th>\n",
       "      <th>visiting_team</th>\n",
       "      <th>competition_time</th>\n",
       "      <th>result</th>\n",
       "      <th>type</th>\n",
       "      <th>create_time</th>\n",
       "      <th>update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>2018-06-14 23:00:00</td>\n",
       "      <td>-</td>\n",
       "      <td>小组赛</td>\n",
       "      <td>2018/4/16 09:40:29</td>\n",
       "      <td>2018/4/16 09:40:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>埃及</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>2018-06-15 20:00:00</td>\n",
       "      <td>-</td>\n",
       "      <td>小组赛</td>\n",
       "      <td>2018/3/12 14:59:10</td>\n",
       "      <td>2018/3/12 14:59:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>埃及</td>\n",
       "      <td>2018-06-20 02:00:00</td>\n",
       "      <td>-</td>\n",
       "      <td>小组赛</td>\n",
       "      <td>2018/3/12 14:59:10</td>\n",
       "      <td>2018/3/12 14:59:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>52</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>2018-06-20 23:00:00</td>\n",
       "      <td>-</td>\n",
       "      <td>小组赛</td>\n",
       "      <td>2018/4/16 09:40:31</td>\n",
       "      <td>2018/4/16 09:40:31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>埃及</td>\n",
       "      <td>2018-06-25 22:00:00</td>\n",
       "      <td>-</td>\n",
       "      <td>小组赛</td>\n",
       "      <td>2018/4/16 09:40:36</td>\n",
       "      <td>2018/4/16 09:40:36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  home_id  visiting_id home_team visiting_team     competition_time  \\\n",
       "0  49        1            2       俄罗斯         沙特阿拉伯  2018-06-14 23:00:00   \n",
       "1  50        3            4        埃及           乌拉圭  2018-06-15 20:00:00   \n",
       "2  51        1            3       俄罗斯            埃及  2018-06-20 02:00:00   \n",
       "3  52        4            2       乌拉圭         沙特阿拉伯  2018-06-20 23:00:00   \n",
       "4  53        2            3     沙特阿拉伯            埃及  2018-06-25 22:00:00   \n",
       "\n",
       "  result type         create_time         update_time  \n",
       "0      -  小组赛  2018/4/16 09:40:29  2018/4/16 09:40:29  \n",
       "1      -  小组赛  2018/3/12 14:59:10  2018/3/12 14:59:10  \n",
       "2      -  小组赛  2018/3/12 14:59:10  2018/3/12 14:59:10  \n",
       "3      -  小组赛  2018/4/16 09:40:31  2018/4/16 09:40:31  \n",
       "4      -  小组赛  2018/4/16 09:40:36  2018/4/16 09:40:36  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "competition_process = pd.read_csv(r\"D:\\worldcup\\competition_process.csv\")\n",
    "competition_process.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 比赛历史信息\n",
    "字段 | 说明 \n",
    "------|------\n",
    "team_id|队伍标题，对应team_info中的id\n",
    "team_name|队伍名称\n",
    "home_team|主场队伍\n",
    "visiting_team|客场队伍\n",
    "type|比赛类型\n",
    "time|比赛时间\n",
    "result|比赛结果\n",
    "foul|犯规 关于team_id的比赛信息（下同）\n",
    "yellow_card|黄牌\n",
    "red_card|红牌\n",
    "possession|控球率\n",
    "shot|射门（射正）\n",
    "pass|传球（成功）\n",
    "pass_rate|传球成功率\n",
    "most|过人次数\n",
    "score|表现评分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>home_team</th>\n",
       "      <th>visiting_team</th>\n",
       "      <th>type</th>\n",
       "      <th>time</th>\n",
       "      <th>result</th>\n",
       "      <th>foul</th>\n",
       "      <th>yellow_card</th>\n",
       "      <th>red_card</th>\n",
       "      <th>possession</th>\n",
       "      <th>shot</th>\n",
       "      <th>pass</th>\n",
       "      <th>pass_rate</th>\n",
       "      <th>most</th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>10</td>\n",
       "      <td>法国</td>\n",
       "      <td>法国</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>国际友谊</td>\n",
       "      <td>2017/3/29 03:00:00</td>\n",
       "      <td>0-2</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.41</td>\n",
       "      <td>8(2)</td>\n",
       "      <td>439(371)</td>\n",
       "      <td>0.85</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699</th>\n",
       "      <td>10</td>\n",
       "      <td>法国</td>\n",
       "      <td>法国</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>国际友谊</td>\n",
       "      <td>2014/9/5 03:00:00</td>\n",
       "      <td>1-0</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.42</td>\n",
       "      <td>10(5)</td>\n",
       "      <td>0(0)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>10</td>\n",
       "      <td>法国</td>\n",
       "      <td>法国</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>欧洲预选</td>\n",
       "      <td>2013/3/27 04:00:00</td>\n",
       "      <td>0-1</td>\n",
       "      <td>21</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.24</td>\n",
       "      <td>15(3)</td>\n",
       "      <td>0(0)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>721</th>\n",
       "      <td>10</td>\n",
       "      <td>法国</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>法国</td>\n",
       "      <td>欧洲预选</td>\n",
       "      <td>2012/10/17 03:00:00</td>\n",
       "      <td>1-1</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0.50</td>\n",
       "      <td>7(3)</td>\n",
       "      <td>0(0)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>725</th>\n",
       "      <td>10</td>\n",
       "      <td>法国</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>法国</td>\n",
       "      <td>欧洲杯</td>\n",
       "      <td>2012/6/24 02:45:00</td>\n",
       "      <td>2-0</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.45</td>\n",
       "      <td>4(1)</td>\n",
       "      <td>0(0)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     team_id team_name home_team visiting_team  type                 time  \\\n",
       "665       10        法国        法国           西班牙  国际友谊   2017/3/29 03:00:00   \n",
       "699       10        法国        法国           西班牙  国际友谊    2014/9/5 03:00:00   \n",
       "717       10        法国        法国           西班牙  欧洲预选   2013/3/27 04:00:00   \n",
       "721       10        法国       西班牙            法国  欧洲预选  2012/10/17 03:00:00   \n",
       "725       10        法国       西班牙            法国   欧洲杯   2012/6/24 02:45:00   \n",
       "\n",
       "    result  foul  yellow_card  red_card  possession   shot      pass  \\\n",
       "665    0-2    17            2         0        0.41   8(2)  439(371)   \n",
       "699    1-0     9            0         0        0.42  10(5)      0(0)   \n",
       "717    0-1    21            3         1        0.24  15(3)      0(0)   \n",
       "721    1-1    10            2         2        0.50   7(3)      0(0)   \n",
       "725    2-0    12            2         1        0.45   4(1)      0(0)   \n",
       "\n",
       "     pass_rate  most  score  \n",
       "665       0.85     1      4  \n",
       "699       0.00     0      2  \n",
       "717       0.00     0      5  \n",
       "721       0.00     0      3  \n",
       "725       0.00     0      3  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "historical_record = pd.read_csv(r\"D:\\worldcup\\historical_record.csv\")\n",
    "del historical_record['id']\n",
    "del historical_record['create_time']\n",
    "del historical_record['update_time']\n",
    "historical_record = historical_record.drop_duplicates() \n",
    "historical_record.loc[(historical_record[\"team_name\"] == '法国') & ((historical_record[\"home_team\"] == '西班牙') | (historical_record[\"visiting_team\"] == '西班牙'))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 队伍信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>group</th>\n",
       "      <th>group_gamecount</th>\n",
       "      <th>group_game_win</th>\n",
       "      <th>group_game_draw</th>\n",
       "      <th>group_game_lose</th>\n",
       "      <th>group_game_goal_diff</th>\n",
       "      <th>score</th>\n",
       "      <th>rank</th>\n",
       "      <th>is_top_16</th>\n",
       "      <th>is_top_8</th>\n",
       "      <th>is_top_4</th>\n",
       "      <th>create_time</th>\n",
       "      <th>update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/3/9 13:59:12</td>\n",
       "      <td>2018/3/9 13:59:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/3/9 09:54:02</td>\n",
       "      <td>2018/3/9 09:54:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>埃及</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/3/7 10:42:31</td>\n",
       "      <td>2018/3/7 10:42:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>A</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/3/7 10:42:36</td>\n",
       "      <td>2018/3/7 10:42:39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>葡萄牙</td>\n",
       "      <td>B</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/3/7 10:42:05</td>\n",
       "      <td>2018/3/7 10:42:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   name group  group_gamecount  group_game_win  group_game_draw  \\\n",
       "0   1    俄罗斯     A                0             NaN              NaN   \n",
       "1   2  沙特阿拉伯     A                0             NaN              NaN   \n",
       "2   3     埃及     A                0             NaN              NaN   \n",
       "3   4    乌拉圭     A                0             NaN              NaN   \n",
       "4   5    葡萄牙     B                0             NaN              NaN   \n",
       "\n",
       "   group_game_lose  group_game_goal_diff  score  rank  is_top_16  is_top_8  \\\n",
       "0              NaN                   NaN      0   NaN        NaN       NaN   \n",
       "1              NaN                   NaN      0   NaN        NaN       NaN   \n",
       "2              NaN                   NaN      0   NaN        NaN       NaN   \n",
       "3              NaN                   NaN      0   NaN        NaN       NaN   \n",
       "4              NaN                   NaN      0   NaN        NaN       NaN   \n",
       "\n",
       "   is_top_4        create_time        update_time  \n",
       "0       NaN  2018/3/9 13:59:12  2018/3/9 13:59:12  \n",
       "1       NaN  2018/3/9 09:54:02  2018/3/9 09:54:02  \n",
       "2       NaN  2018/3/7 10:42:31  2018/3/7 10:42:33  \n",
       "3       NaN  2018/3/7 10:42:36  2018/3/7 10:42:39  \n",
       "4       NaN  2018/3/7 10:42:05  2018/3/7 10:42:05  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_info = pd.read_csv(r\"D:\\worldcup\\team_info.csv\")\n",
    "team_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 世界排名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>local_rank</th>\n",
       "      <th>world_rank</th>\n",
       "      <th>rating</th>\n",
       "      <th>average_rank</th>\n",
       "      <th>average_rating</th>\n",
       "      <th>one_year_rank_change</th>\n",
       "      <th>one_year_rating_change</th>\n",
       "      <th>matches_total</th>\n",
       "      <th>matches_home</th>\n",
       "      <th>matches_away</th>\n",
       "      <th>matches_neutral</th>\n",
       "      <th>matches_win</th>\n",
       "      <th>matches_lose</th>\n",
       "      <th>matches_draw</th>\n",
       "      <th>goal_for</th>\n",
       "      <th>goal_against</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>巴西</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2113</td>\n",
       "      <td>4</td>\n",
       "      <td>1993</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>960</td>\n",
       "      <td>336</td>\n",
       "      <td>312</td>\n",
       "      <td>312</td>\n",
       "      <td>607</td>\n",
       "      <td>157</td>\n",
       "      <td>196</td>\n",
       "      <td>2085</td>\n",
       "      <td>880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>德国</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2109</td>\n",
       "      <td>8</td>\n",
       "      <td>1907</td>\n",
       "      <td>1</td>\n",
       "      <td>71</td>\n",
       "      <td>938</td>\n",
       "      <td>407</td>\n",
       "      <td>380</td>\n",
       "      <td>151</td>\n",
       "      <td>548</td>\n",
       "      <td>200</td>\n",
       "      <td>190</td>\n",
       "      <td>2114</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2031</td>\n",
       "      <td>7</td>\n",
       "      <td>1936</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "      <td>678</td>\n",
       "      <td>297</td>\n",
       "      <td>266</td>\n",
       "      <td>115</td>\n",
       "      <td>397</td>\n",
       "      <td>129</td>\n",
       "      <td>152</td>\n",
       "      <td>1347</td>\n",
       "      <td>615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>葡萄牙</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1995</td>\n",
       "      <td>19</td>\n",
       "      <td>1779</td>\n",
       "      <td>4</td>\n",
       "      <td>64</td>\n",
       "      <td>593</td>\n",
       "      <td>277</td>\n",
       "      <td>225</td>\n",
       "      <td>91</td>\n",
       "      <td>284</td>\n",
       "      <td>173</td>\n",
       "      <td>136</td>\n",
       "      <td>991</td>\n",
       "      <td>696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>法国</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1989</td>\n",
       "      <td>17</td>\n",
       "      <td>1771</td>\n",
       "      <td>-1</td>\n",
       "      <td>-20</td>\n",
       "      <td>823</td>\n",
       "      <td>422</td>\n",
       "      <td>308</td>\n",
       "      <td>93</td>\n",
       "      <td>399</td>\n",
       "      <td>252</td>\n",
       "      <td>172</td>\n",
       "      <td>1451</td>\n",
       "      <td>1171</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id team_name  local_rank  world_rank  rating  average_rank  average_rating  \\\n",
       "0   1        巴西           1           1    2113             4            1993   \n",
       "1   2        德国           2           2    2109             8            1907   \n",
       "2   3       西班牙           3           3    2031             7            1936   \n",
       "3   4       葡萄牙           4           4    1995            19            1779   \n",
       "4   5        法国           5           5    1989            17            1771   \n",
       "\n",
       "   one_year_rank_change  one_year_rating_change  matches_total  matches_home  \\\n",
       "0                     0                      22            960           336   \n",
       "1                     1                      71            938           407   \n",
       "2                     3                      52            678           297   \n",
       "3                     4                      64            593           277   \n",
       "4                    -1                     -20            823           422   \n",
       "\n",
       "   matches_away  matches_neutral  matches_win  matches_lose  matches_draw  \\\n",
       "0           312              312          607           157           196   \n",
       "1           380              151          548           200           190   \n",
       "2           266              115          397           129           152   \n",
       "3           225               91          284           173           136   \n",
       "4           308               93          399           252           172   \n",
       "\n",
       "   goal_for  goal_against  \n",
       "0      2085           880  \n",
       "1      2114          1100  \n",
       "2      1347           615  \n",
       "3       991           696  \n",
       "4      1451          1171  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_rank = pd.read_csv(r\"D:\\worldcup\\team_rank.csv\")\n",
    "team_rank.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 算法"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime\n",
    "import numpy as np\n",
    "\n",
    "def sigmoid(inX):  \n",
    "    return 1.0/(1+np.exp(-inX))  \n",
    "\n",
    "def goal_fiff(team1, team2, competition_time):\n",
    "    #historical_record = pd.read_csv(r\"D:\\worldcup\\historical_record.csv\")\n",
    "    historical_record = pd.read_csv(r\"D:\\worldcup\\historical_record.csv\")\n",
    "    del historical_record['id']\n",
    "    del historical_record['create_time']\n",
    "    del historical_record['update_time']\n",
    "    #去重\n",
    "    historical_record = historical_record.drop_duplicates().reset_index(drop = True)\n",
    "    #处理result为净胜球    \n",
    "    for i in range(len(historical_record)):\n",
    "        num1 = historical_record.loc[i, 'result'].split('-')[0]\n",
    "        num2 = historical_record.loc[i, 'result'].split('-')[1]\n",
    "        if historical_record.loc[i, 'home_team'] == historical_record.loc[i, 'team_name']:\n",
    "            historical_record.loc[i, 'result'] = int(num1) - int(num2)\n",
    "        else:\n",
    "            historical_record.loc[i, 'result'] = int(num2) - int(num1)\n",
    "    #选出team1和team2\n",
    "    historical_record_1 = historical_record.loc[historical_record[\"team_name\"] == team1]\n",
    "    historical_record_2 = historical_record.loc[historical_record[\"team_name\"] == team2]\n",
    "    e_df = set(historical_record_1['home_team'].tolist()).union(set(historical_record_1['visiting_team'].tolist()))\n",
    "    s_df = set(historical_record_2['home_team'].tolist()).union(set(historical_record_2['visiting_team'].tolist()))\n",
    "    e_union_s = e_df.intersection(s_df)\n",
    "    #print(e_union_s)\n",
    "    #选出即和team1交手又和team2交手的队伍\n",
    "    int_inf_2 = historical_record_2.loc[historical_record_2[\"visiting_team\"].isin(e_union_s) | historical_record_2[\"home_team\"].isin(e_union_s)]\n",
    "    int_inf_1 = historical_record_1.loc[historical_record_1[\"visiting_team\"].isin(e_union_s) | historical_record_1[\"home_team\"].isin(e_union_s)]\n",
    "    concat_e_s = pd.concat([int_inf_1, int_inf_2]).reset_index(drop=True)  \n",
    "    \n",
    "    #print(e_df)\n",
    "    #设置比赛开始时间\n",
    "    concat_e_s_sle = concat_e_s.loc[: , ['team_name', 'home_team', 'visiting_team', 'time', 'result', 'score']]\n",
    "    concat_e_s_sle['time'] = pd.to_datetime(concat_e_s_sle['time'])\n",
    "    start_date_str = competition_time\n",
    "    start_date = datetime.datetime.strptime(start_date_str, '%Y-%m-%d %H:%M:%S')\n",
    "    #sigmode处理时间，得出权重\n",
    "    concat_e_s_sle['sec_from_start_to_data'] = (concat_e_s_sle.loc[:, 'time']-start_date).dt.total_seconds()  \n",
    "    concat_e_s_sle['sfstd_non'] = concat_e_s_sle.loc[:, ['sec_from_start_to_data']].apply(lambda x: (x - np.mean(x)) / (np.std(x)))\n",
    "\n",
    "    concat_e_s_sle['wight'] = sigmoid(concat_e_s_sle['sfstd_non'])\n",
    "    #净胜球与权重结合\n",
    "    concat_e_s_sle['result_wight'] = concat_e_s_sle['result'] * concat_e_s_sle['wight']\n",
    "    #计算结合平均值\n",
    "    union_1 = pd.DataFrame({'team_name':[],'oppose_team':[],'mean_result_1':[]})\n",
    "    union_2 = pd.DataFrame({'team_name':[],'oppose_team':[],'mean_result_2':[]})\n",
    "    for i in e_union_s:\n",
    "        if i != team1:\n",
    "            mean_1 = concat_e_s_sle.loc[(concat_e_s_sle['team_name'] == team1)&((concat_e_s_sle['home_team'] == i) | (concat_e_s_sle['visiting_team'] == i))].loc[:, 'result_wight'].mean()\n",
    "            union_1 = pd.concat([union_1, pd.DataFrame({'team_name':[team1],'oppose_team':[i],'mean_result_1':[mean_1]})])\n",
    "        if i != team2:\n",
    "            mean_2 = concat_e_s_sle.loc[(concat_e_s_sle['team_name'] == team2)&((concat_e_s_sle['home_team'] == i) | (concat_e_s_sle['visiting_team'] == i))].loc[:, 'result_wight'].mean()\n",
    "            union_2 = pd.concat([union_2, pd.DataFrame({'team_name':[team2],'oppose_team':[i],'mean_result_2':[mean_2]})])\n",
    "        \n",
    "        \n",
    "    union = union_1.merge(union_2, on = 'oppose_team')\n",
    "    #print(union)\n",
    "    \n",
    "    #team1与team2直接对抗结果\n",
    "    mean_1_2 = concat_e_s_sle.loc[(concat_e_s_sle['team_name'] == team1)&((concat_e_s_sle['home_team'] == team2) | (concat_e_s_sle['visiting_team'] == team2))].loc[:, 'result_wight'].mean()\n",
    "    op_1_2 = pd.DataFrame({'team_name':[team1],'oppose_team':[team2],'mean_result_1':[mean_1_2]})\n",
    "    mean_2_1 = concat_e_s_sle.loc[(concat_e_s_sle['team_name'] == team2)&((concat_e_s_sle['home_team'] == team1) | (concat_e_s_sle['visiting_team'] == team1))].loc[:, 'result_wight'].mean()\n",
    "    op_2_1 = pd.DataFrame({'team_name':[team2],'oppose_team':[team1],'mean_result_2':[mean_2_1]})\n",
    "    #合并所有\n",
    "    union_all = pd.concat([union, op_2_1, op_1_2]).fillna(0)\n",
    "    #print(pd.concat([union, op_2_1, op_1_2]).fillna(0))\n",
    "    \n",
    "    res = (union_all['mean_result_1'] - union_all['mean_result_2']).mean()\n",
    "    res_e_s = pd.DataFrame({'team1':[team1], 'team2':[team2], 'competition_time':[competition_time], '净胜球':[res]})\n",
    "    return res_e_s\n",
    "\n",
    "#goal_fiff('西班牙', '法国', '2018-06-14 23:00:00')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>competition_time</th>\n",
       "      <th>team1</th>\n",
       "      <th>team2</th>\n",
       "      <th>净胜球</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-14 23:00:00</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>0.824845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-15 20:00:00</td>\n",
       "      <td>埃及</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>-0.275923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-20 02:00:00</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>埃及</td>\n",
       "      <td>0.299574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-20 23:00:00</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>0.498543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-25 22:00:00</td>\n",
       "      <td>沙特阿拉伯</td>\n",
       "      <td>埃及</td>\n",
       "      <td>-0.141609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-25 22:00:00</td>\n",
       "      <td>俄罗斯</td>\n",
       "      <td>乌拉圭</td>\n",
       "      <td>0.098682</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-15 23:00:00</td>\n",
       "      <td>摩洛哥</td>\n",
       "      <td>伊朗</td>\n",
       "      <td>-0.107328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-16 02:00:00</td>\n",
       "      <td>葡萄牙</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>-0.464226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-20 20:00:00</td>\n",
       "      <td>葡萄牙</td>\n",
       "      <td>摩洛哥</td>\n",
       "      <td>0.277806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-21 02:00:00</td>\n",
       "      <td>伊朗</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>-0.326099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-26 02:00:00</td>\n",
       "      <td>伊朗</td>\n",
       "      <td>葡萄牙</td>\n",
       "      <td>-0.063409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-26 02:00:00</td>\n",
       "      <td>西班牙</td>\n",
       "      <td>摩洛哥</td>\n",
       "      <td>0.373414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-16 18:00:00</td>\n",
       "      <td>法国</td>\n",
       "      <td>澳大利亚</td>\n",
       "      <td>0.880400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-16 23:59:00</td>\n",
       "      <td>秘鲁</td>\n",
       "      <td>丹麦</td>\n",
       "      <td>-0.496810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-21 20:00:00</td>\n",
       "      <td>丹麦</td>\n",
       "      <td>澳大利亚</td>\n",
       "      <td>0.541176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-21 23:00:00</td>\n",
       "      <td>法国</td>\n",
       "      <td>秘鲁</td>\n",
       "      <td>0.561171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-26 22:00:00</td>\n",
       "      <td>澳大利亚</td>\n",
       "      <td>秘鲁</td>\n",
       "      <td>-0.520624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-26 22:00:00</td>\n",
       "      <td>丹麦</td>\n",
       "      <td>法国</td>\n",
       "      <td>-0.621076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-16 21:00:00</td>\n",
       "      <td>阿根廷</td>\n",
       "      <td>冰岛</td>\n",
       "      <td>0.710319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-17 03:00:00</td>\n",
       "      <td>克罗地亚</td>\n",
       "      <td>尼日利亚</td>\n",
       "      <td>-0.000198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-22 02:00:00</td>\n",
       "      <td>阿根廷</td>\n",
       "      <td>克罗地亚</td>\n",
       "      <td>0.264655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-22 23:00:00</td>\n",
       "      <td>尼日利亚</td>\n",
       "      <td>冰岛</td>\n",
       "      <td>0.122830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-27 02:00:00</td>\n",
       "      <td>冰岛</td>\n",
       "      <td>克罗地亚</td>\n",
       "      <td>-0.089284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-27 02:00:00</td>\n",
       "      <td>尼日利亚</td>\n",
       "      <td>阿根廷</td>\n",
       "      <td>-0.392545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-17 20:00:00</td>\n",
       "      <td>哥斯达黎加</td>\n",
       "      <td>塞尔维亚</td>\n",
       "      <td>-0.074098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-18 02:00:00</td>\n",
       "      <td>巴西</td>\n",
       "      <td>瑞士</td>\n",
       "      <td>0.341689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-22 20:00:00</td>\n",
       "      <td>巴西</td>\n",
       "      <td>哥斯达黎加</td>\n",
       "      <td>0.825737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-23 02:00:00</td>\n",
       "      <td>塞尔维亚</td>\n",
       "      <td>瑞士</td>\n",
       "      <td>-0.002520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-28 02:00:00</td>\n",
       "      <td>塞尔维亚</td>\n",
       "      <td>巴西</td>\n",
       "      <td>-0.799753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-28 02:00:00</td>\n",
       "      <td>瑞士</td>\n",
       "      <td>哥斯达黎加</td>\n",
       "      <td>0.078777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-17 23:00:00</td>\n",
       "      <td>德国</td>\n",
       "      <td>墨西哥</td>\n",
       "      <td>0.382727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-18 20:00:00</td>\n",
       "      <td>瑞典</td>\n",
       "      <td>韩国</td>\n",
       "      <td>0.187302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-23 23:00:00</td>\n",
       "      <td>韩国</td>\n",
       "      <td>墨西哥</td>\n",
       "      <td>-0.221690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-24 02:00:00</td>\n",
       "      <td>德国</td>\n",
       "      <td>瑞典</td>\n",
       "      <td>0.789900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-27 22:00:00</td>\n",
       "      <td>韩国</td>\n",
       "      <td>德国</td>\n",
       "      <td>-0.723100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-27 22:00:00</td>\n",
       "      <td>墨西哥</td>\n",
       "      <td>瑞典</td>\n",
       "      <td>-0.204403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-18 23:00:00</td>\n",
       "      <td>比利时</td>\n",
       "      <td>巴拿马</td>\n",
       "      <td>0.369149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-19 02:00:00</td>\n",
       "      <td>突尼斯</td>\n",
       "      <td>英格兰</td>\n",
       "      <td>-0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-23 20:00:00</td>\n",
       "      <td>比利时</td>\n",
       "      <td>突尼斯</td>\n",
       "      <td>0.378684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-24 20:00:00</td>\n",
       "      <td>英格兰</td>\n",
       "      <td>巴拿马</td>\n",
       "      <td>0.620594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-29 02:00:00</td>\n",
       "      <td>英格兰</td>\n",
       "      <td>比利时</td>\n",
       "      <td>-0.190353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-29 02:00:00</td>\n",
       "      <td>巴拿马</td>\n",
       "      <td>突尼斯</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-19 20:00:00</td>\n",
       "      <td>哥伦比亚</td>\n",
       "      <td>日本</td>\n",
       "      <td>0.677823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-19 23:00:00</td>\n",
       "      <td>波兰</td>\n",
       "      <td>塞内加尔</td>\n",
       "      <td>-0.064392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-24 23:00:00</td>\n",
       "      <td>日本</td>\n",
       "      <td>塞内加尔</td>\n",
       "      <td>0.050117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-25 02:00:00</td>\n",
       "      <td>波兰</td>\n",
       "      <td>哥伦比亚</td>\n",
       "      <td>-0.429224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-28 22:00:00</td>\n",
       "      <td>日本</td>\n",
       "      <td>波兰</td>\n",
       "      <td>0.111394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-28 22:00:00</td>\n",
       "      <td>塞内加尔</td>\n",
       "      <td>哥伦比亚</td>\n",
       "      <td>-0.746772</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      competition_time  team1  team2       净胜球\n",
       "0  2018-06-14 23:00:00    俄罗斯  沙特阿拉伯  0.824845\n",
       "0  2018-06-15 20:00:00     埃及    乌拉圭 -0.275923\n",
       "0  2018-06-20 02:00:00    俄罗斯     埃及  0.299574\n",
       "0  2018-06-20 23:00:00    乌拉圭  沙特阿拉伯  0.498543\n",
       "0  2018-06-25 22:00:00  沙特阿拉伯     埃及 -0.141609\n",
       "0  2018-06-25 22:00:00    俄罗斯    乌拉圭  0.098682\n",
       "0  2018-06-15 23:00:00    摩洛哥     伊朗 -0.107328\n",
       "0  2018-06-16 02:00:00    葡萄牙    西班牙 -0.464226\n",
       "0  2018-06-20 20:00:00    葡萄牙    摩洛哥  0.277806\n",
       "0  2018-06-21 02:00:00     伊朗    西班牙 -0.326099\n",
       "0  2018-06-26 02:00:00     伊朗    葡萄牙 -0.063409\n",
       "0  2018-06-26 02:00:00    西班牙    摩洛哥  0.373414\n",
       "0  2018-06-16 18:00:00     法国   澳大利亚  0.880400\n",
       "0  2018-06-16 23:59:00     秘鲁     丹麦 -0.496810\n",
       "0  2018-06-21 20:00:00     丹麦   澳大利亚  0.541176\n",
       "0  2018-06-21 23:00:00     法国     秘鲁  0.561171\n",
       "0  2018-06-26 22:00:00   澳大利亚     秘鲁 -0.520624\n",
       "0  2018-06-26 22:00:00     丹麦     法国 -0.621076\n",
       "0  2018-06-16 21:00:00    阿根廷     冰岛  0.710319\n",
       "0  2018-06-17 03:00:00   克罗地亚   尼日利亚 -0.000198\n",
       "0  2018-06-22 02:00:00    阿根廷   克罗地亚  0.264655\n",
       "0  2018-06-22 23:00:00   尼日利亚     冰岛  0.122830\n",
       "0  2018-06-27 02:00:00     冰岛   克罗地亚 -0.089284\n",
       "0  2018-06-27 02:00:00   尼日利亚    阿根廷 -0.392545\n",
       "0  2018-06-17 20:00:00  哥斯达黎加   塞尔维亚 -0.074098\n",
       "0  2018-06-18 02:00:00     巴西     瑞士  0.341689\n",
       "0  2018-06-22 20:00:00     巴西  哥斯达黎加  0.825737\n",
       "0  2018-06-23 02:00:00   塞尔维亚     瑞士 -0.002520\n",
       "0  2018-06-28 02:00:00   塞尔维亚     巴西 -0.799753\n",
       "0  2018-06-28 02:00:00     瑞士  哥斯达黎加  0.078777\n",
       "0  2018-06-17 23:00:00     德国    墨西哥  0.382727\n",
       "0  2018-06-18 20:00:00     瑞典     韩国  0.187302\n",
       "0  2018-06-23 23:00:00     韩国    墨西哥 -0.221690\n",
       "0  2018-06-24 02:00:00     德国     瑞典  0.789900\n",
       "0  2018-06-27 22:00:00     韩国     德国 -0.723100\n",
       "0  2018-06-27 22:00:00    墨西哥     瑞典 -0.204403\n",
       "0  2018-06-18 23:00:00    比利时    巴拿马  0.369149\n",
       "0  2018-06-19 02:00:00    突尼斯    英格兰 -0.333333\n",
       "0  2018-06-23 20:00:00    比利时    突尼斯  0.378684\n",
       "0  2018-06-24 20:00:00    英格兰    巴拿马  0.620594\n",
       "0  2018-06-29 02:00:00    英格兰    比利时 -0.190353\n",
       "0  2018-06-29 02:00:00    巴拿马    突尼斯  0.000000\n",
       "0  2018-06-19 20:00:00   哥伦比亚     日本  0.677823\n",
       "0  2018-06-19 23:00:00     波兰   塞内加尔 -0.064392\n",
       "0  2018-06-24 23:00:00     日本   塞内加尔  0.050117\n",
       "0  2018-06-25 02:00:00     波兰   哥伦比亚 -0.429224\n",
       "0  2018-06-28 22:00:00     日本     波兰  0.111394\n",
       "0  2018-06-28 22:00:00   塞内加尔   哥伦比亚 -0.746772"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "competition_process = pd.read_csv(r\"D:\\worldcup\\competition_process.csv\")\n",
    "result = pd.DataFrame({'team1':[] , 'team2':[], 'competition_time':[], '净胜球':[]})\n",
    "for i in range(len(competition_process)):\n",
    "    result = pd.concat([result, goal_fiff(competition_process.loc[i, 'home_team'], competition_process.loc[i, 'visiting_team'], competition_process.loc[i, 'competition_time'])])\n",
    "    #print(i)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "result.to_csv(r\"D:\\worldcup\\result.csv\", index_label=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
